﻿@page "/Form5"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form5> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总, 业务经理, 业务员")]
<style>
    .multi-select {
        --bb-multi-select-min-height: 26px;
    }
</style>

<div class="row g-1">
    <div class="col-6 col-sm-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-6 col-sm-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-12 col-sm-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@部门Items" @bind-Value="@选中部门List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
</div>
<div class="row g-1">
    <div class="col-6 col-md-6 col-sm-3">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" IsVertical="false" />
    </div>
    <div class="col-3 col-sm-4 col-md-3 col-lg-auto"><Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button></div>
</div>
<Table TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="true"
       OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" IsStriped="true" Height="500" IsFixedHeader="true">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>

<Table TItem="DynamicObject" DynamicContext="DataTableDynamicContextDetail"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="true"
       OnExportAsync="OnExportDetailAsync" OnSort="OnSortDetail" ShowFooter="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" IsStriped="true" Height="500" IsFixedHeader="true">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContextDetail != null && DataTableDynamicContextDetail.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContextDetail.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContextDetail.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>

@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<View_Depart> View_DepartList = new List<View_Depart>();
    //时间范围
    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 部门Items => View_DepartList.Select(i => new SelectedItem(i.cDepCode!, i.cDepName!)).ToList();
    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "产品", Value = "产品" },
        new SelectedItem { Text = "类别", Value = "类别"}
    });


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中部门List { get; set; }
    private string 选中部门InStr = "";
    private string 选中查询类别 { get; set; }

    //表格数据
    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }
    private DataTableDynamicContext? DataTableDynamicContextDetail { get; set; }

    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });

        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List<View_Depart> deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        View_DepartList = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();
        // //加一个请选择
        // View_DepartList.Insert(0, new View_Depart { cDepCode = "", cDepName = "请选择" });

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中部门List != null)
        {
            foreach (var item in 选中部门List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中部门InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"原06代理业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();

        DataTableDynamicContext = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
            });
        StateHasChanged();
        return sortString;
    }

    private async Task<bool> OnExportDetailAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContextDetail.GetItems(), context.Columns, $"原06代理业务数据穿透分析明细_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private string OnSortDetail(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContextDetail.DataTable.Clone();

        DataView dv = DataTableDynamicContextDetail.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();

        DataTableDynamicContextDetail = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
            });
        StateHasChanged();
        return sortString;
    }

    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;
        // string loginuser = username;
        string loginuser = "admin";
        sql = "SELECT DepartmentName FROM [netcoredev].[dbo].[View_PersonDepart] where  cPersonName='" + loginuser + "'";
        string depart = _SqlSugarScope.Ado.GetString(sql);
        string guid = Guid.NewGuid().ToString();  ///本次查询统一的
        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";

        sql = "select docyear, dbname FROM      Tset where docyear='" + 年度 + "'";
        DataTable tb1 = _SqlSugarScope.Ado.GetDataTable(sql);
        if (tb1.Rows.Count > 0)
        {
            今年sql = tb1.Rows[0]["dbname"].ToString();
            sql = "delete T客户发货 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //处理今年(今年sql)
            //1,塞入空客户
            #region
            sql = "insert into  T客户发货 (guid, usercode, Depart, cCusCode, cDLCode,irowno, cPersonCode, dDate ,";
            sql += " 本期销售额, 同期销售额)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " iMoney,0 from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);

            sql = "insert into  T客户发货 (guid, usercode, Depart, cCusCode, cDLCode,irowno, cPersonCode, dDate ,";
            sql += " 本期销售额, 同期销售额)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " 0,iMoney from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新本期销售额
            #region
            // sql = " update a set 本期销售额=b.[iMoney]   ";
            // sql += " from  T客户发货 a ,View_发票明细 b ";
            // sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新上期销售额
            #region
            // sql = " update a set 同期销售额=b.[iMoney]   ";
            // sql += " from  T客户发货 a ,View_发票明细 b ";
            // sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 去年 + "' and b.dDate<='" + 去年结束日期 + "'and b.dDate>='" + 去年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //2塞入空客户
            #region
            sql = "delete T客户回款 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            sql = "insert into  T客户回款 (guid, usercode,  Depart, cCusCode, iid, id, cPersonCode,dDate,  本期回款额, 同期回款额)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cDwCode ,iID,id,cPerson,dDate,";
            sql += " iRAmt,0 from View_收款明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cDwCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cDwCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 年度 + "' and  dDate<='" + 今年结束日期 + "'and  dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);

            sql = "insert into  T客户回款 (guid, usercode,  Depart, cCusCode, iid, id, cPersonCode,dDate,  本期回款额, 同期回款额)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cDwCode ,iID,id,cPerson,dDate,";
            sql += " 0,iRAmt from View_收款明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cDwCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cDwCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and  period='" + 去年 + "' and  dDate<='" + 去年结束日期 + "'and  dDate>='" + 去年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            // //开始更新本期回款
            // #region
            // sql = " update a set  本期回款额=b.iRAmt   ";
            // sql += " from  T客户回款 a ,View_收款明细 b ";
            // sql += " where a.guid='" + guid + "' and a.iid=b.iid and a.id=b.id and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            // #endregion
            // //开始更新上期回款
            // #region
            // sql = " update a set 同期回款额=b.[iRAmt]   ";
            // sql += " from  T客户回款 a ,[dbo].[View_收款明细] b ";
            // sql += " where a.guid='" + guid + "' and a.iid=b.iid and a.id=b.id and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 去年 + "' and b.dDate<='" + 去年结束日期 + "'and b.dDate>='" + 去年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            // #endregion
            //更新客户
            //sql = "update  a set a.代理商名称=b.cCusName ";
            //sql += " from T客户发货 a, View_Custom b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            //_SqlSugarScope.Ado.ExecuteCommand(sql);
            ////更新销售员
            //sql = "update  a set a.销售员=b.cPersonName ";
            //sql += " from T客户发货 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            //_SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新部门
            sql = "update  a set a.部门=b.DepartmentName,a.Depart=b.[cDepCode] ";
            sql += " from T客户发货 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新事业部（就是一级部门）
            sql = "update  a set a.事业部=b.事业部 ";
            sql += " from T客户发货 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            ////更新客户
            //sql = "update  a set a.代理商名称=b.cCusName ";
            //sql += " from T客户回款 a, View_Custom b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            //_SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新销售员
            //sql = "update  a set a.销售员=b.cPersonName ";
            //sql += " from T客户回款 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            //_SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新部门
            sql = "update  a set a.部门=b.DepartmentName,a.Depart=b.[cDepCode] ";
            sql += " from T客户回款 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新事业部（就是一级部门）
            sql = "update  a set a.事业部=b.事业部 ";
            sql += " from T客户回款 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //显示数据
            sql = @"select t.部门,t.[Depart],t.事业部,sum(本期销售) 今年业绩, sum(同期销售) 往年业绩 ,
sum(本期回款) 回款金额,sum(同期回款) 上年回款金额,(sum(本期销售)-sum(同期销售)) 业绩差异,(sum(本期回款)-sum(同期回款) ) 回款差异
from (SELECT 事业部,substring([Depart] ,0,4) Depart,部门,sum(本期销售额) 本期销售, sum(同期销售额) 同期销售 ,0 本期回款 ,0 同期回款
FROM T客户发货 where guid='" + guid + "' ";
            // Session["代理商名称"] = this.TextBox客户.Text.Trim().Replace("-", "");
            if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
            {
                sql += " and 代理商名称 like '%" + this.选中客户.Text + "%'";
            }
            sql += " and 部门 in " + 部门清单;
            sql += " group by 事业部,substring([Depart] ,0,4) ,部门";
            sql += @" union all
SELECT 事业部,substring([Depart] ,0,4) Depart,部门, 0 本期销售, 0 同期销售 ,sum([本期回款额]) 本期回款 ,sum([同期回款额]) 同期回款
FROM [T客户回款] where guid='" + guid + "'";
            if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
            {
                sql += " and 代理商名称 like '%" + this.选中客户.Text + "%'";
            }
            sql += " and 部门 in " + 部门清单;
            sql += " group by 事业部,substring([Depart] ,0,4),部门)  t group by 事业部,t.[Depart],部门 order by t.[Depart]";
            DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);
            sql = " delete T客户发货 where  guid='" + guid + "' ";
            sql += " delete T客户回款 where  guid='" + guid + "' ";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ, (context, col) =>
           {
               //所有列可排序
               col.Sortable = true;
               col.DefaultSort = true;
               var propertyName = col.GetFieldName();
               //如果是部门列，则按钮形式显示
               if (propertyName == "事业部")
               {
                   //获取当前列的值
                   //新建Template
                   col.Template = (item) =>
     {
         return new RenderFragment(builder =>
         {
             //通过反射获取当前列的值
             var value = item.GetType().GetProperty(propertyName)?.GetValue(item);
             builder.OpenComponent(0, typeof(Button));
             builder.AddAttribute(1, "Color", Color.Primary);
             // builder.AddAttribute(2, "Size", ButtonSize.Small);
             builder.AddAttribute(3, "ChildContent", (RenderFragment)(builder =>
             {
                 builder.AddContent(0, value);
             }));

             //加点击事件

             builder.AddAttribute(0, nameof(Button.OnClick), EventCallback.Factory.Create<MouseEventArgs>(this, (e) =>
             {
                 //获取按钮text
                 var text = value.ToString();
                 string 事业部 = text;
                 chaxun(事业部);
             }));


             // builder.AddAttribute(4, nameof(Button.OnClick), OnClick);

             builder.CloseComponent();
         });
     };


               }
           });
            await ToastService.Information("提示", "查询成功");
        }
        else
        {
            await ToastService.Success("提示", "选择的日期有误", autoHide: true);
        }
    }

    private decimal su1;
    private decimal su2;
    private decimal su3;
    private decimal su4;
    private decimal su5;
    private decimal su6;
    private decimal su7;
    private decimal su8;
    private decimal sm1;
    private decimal sm2;
    private decimal sm3;
    private decimal sm4;
    private decimal sm5;
    private decimal sm6;
    private decimal sm7;
    private decimal sm8;

    private async void chaxun(string 事业部)
    {
        su1 = 0;
        su2 = 0;
        su3 = 0;
        su4 = 0;
        su5 = 0;
        su6 = 0;
        su7 = 0;
        su8 = 0;
        sm1 = 0;
        sm2 = 0;
        sm3 = 0;
        sm4 = 0;
        sm5 = 0;
        sm6 = 0;
        sm7 = 0;
        sm8 = 0;
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;
        string 代理商名称;
        string loginuser = username;
        sql = "SELECT DepartmentName FROM [netcoredev].[dbo].[View_PersonDepart] where  cPersonName='" + loginuser + "'";
        string depart = _SqlSugarScope.Ado.GetString(sql);
        string guid = Guid.NewGuid().ToString();  ///本次查询统一的
        sql = "select docyear, dbname FROM      Tset where docyear='" + 年度 + "'";
        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";

        DataTable tb1 = _SqlSugarScope.Ado.GetDataTable(sql);
        if (tb1.Rows.Count > 0)
        {
            今年sql = tb1.Rows[0]["dbname"].ToString();
            sql = "delete T客户发货 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //处理今年(今年sql)
            //1,塞入空客户
            #region
            sql = "insert into  T客户发货 (guid, usercode, Depart, cCusCode, cDLCode,irowno, cPersonCode,dDate ,";
            sql += " 本期销售额, 同期销售额,cInvCode )";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " iMoney,0,cInvCode  from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);

            sql = "insert into  T客户发货 (guid, usercode, Depart, cCusCode, cDLCode,irowno, cPersonCode,dDate ,";
            sql += " 本期销售额, 同期销售额,cInvCode )";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " 0,iMoney,cInvCode  from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新本期销售额
            #region
            // sql = " update a set 本期销售额=b.[iMoney]   ";
            // sql += " from  T客户发货 a ,View_发票明细 b ";
            // sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新上期销售额
            #region
            // sql = " update a set 同期销售额=b.[iMoney]   ";
            // sql += " from  T客户发货 a ,View_发票明细 b ";
            // sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            // sql += " and b.period='" + 去年 + "' and b.dDate<='" + 去年结束日期 + "'and b.dDate>='" + 去年开始日期 + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新本期退货数量
            #region
            sql = " update a set 本期退货次数=1 ,同期退货次数=0   ";
            sql += " from  T客户发货 a ,View_发票明细 b ";
            sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "' and b.[iMoney]<0";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新上期销售额
            #region
            sql = " update a set 同期退货次数=1 ,本期退货次数=0  ";
            sql += " from  T客户发货 a ,View_发票明细 b ";
            sql += " where a.guid='" + guid + "' and a.cDLCode=b.cDLCode and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            sql += " and b.period='" + 去年 + "' and b.dDate<='" + 去年结束日期 + "'and b.dDate>='" + 去年开始日期 + "' and b.[iMoney]<0";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //更新客户
            sql = "update  a set a.代理商名称=b.cCusName ,a.销售员=b.cPersonName,a.cPersonCode=b.cCusPPerson ";
            sql += " from T客户发货 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            ////更新销售员
            // sql = "update  a set a.销售员=b.cPersonName ";
            // sql += " from T客户发货 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新部门
            sql = "update  a set a.部门=b.DepartmentName,a.Depart=b.[cDepCode] ";
            sql += " from T客户发货 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新事业部（就是一级部门）
            sql = "update  a set a.事业部=b.事业部 ";
            sql += " from T客户发货 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新产品
            sql = "update  a set a.产品名称=b.cInvName,a.规格=b.cInvStd,a.类别=b.类别名称 ";
            sql += " from T客户发货 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //显示数据
            if (选中查询类别 == "产品")
            {
                //产品
                sql = " SELECT   代理商名称,部门,销售员,类别,产品名称,sum(本期销售额) 今年业绩, sum(同期销售额) 往年业绩 ,";
                sql += " sum(本期销售额)-sum(同期销售额) 业绩差异,sum(isnull(本期退货次数,0)) 今年退货次数,sum(isnull(同期退货次数,0)) 往年退货次数";
                sql += " FROM T客户发货 where guid='" + guid + "' ";
                if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                {
                    sql += " and 代理商名称 like '%" + this.选中客户?.Text + "%'";
                }
                sql += " and 部门 in " + 部门清单;
                sql += " and 事业部='" + 事业部 + "'";
                sql += " group by 代理商名称,部门,销售员,类别,产品名称 ";


                DataTable dataTableHZ1 = _SqlSugarScope.Ado.GetDataTable(sql);
                if (dataTableHZ1 != null && dataTableHZ1.Rows.Count>0)
                {
                    if (dataTableHZ1.Columns.Contains("序号") == false)
                    {
                        dataTableHZ1.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                    }
                   
                for (int i = 0; i < dataTableHZ1.Rows.Count; i++)
                {
                    dataTableHZ1.Rows[i]["序号"] = i + 1;
                }

                }
                sql = " delete T客户发货 where  guid='" + guid + "' ";

                _SqlSugarScope.Ado.ExecuteCommand(sql);

                //dt第一列插入序号列
                
                DataTableDynamicContextDetail = new DataTableDynamicContext(dataTableHZ1, (context, col) =>
                {
                    //所有列可排序
                    col.Sortable = true;
                    col.DefaultSort = true;
                });
            }
            else
            {
                //类别
                sql = " SELECT   代理商名称,部门,销售员,类别,sum(本期销售额) 今年业绩, sum(同期销售额) 往年业绩 ,";
                sql += " sum(本期销售额)-sum(同期销售额) 业绩差异,sum(isnull(本期退货次数,0)) 今年退货次数,sum(isnull(同期退货次数,0)) 往年退货次数";
                sql += " FROM T客户发货 where guid='" + guid + "' ";
                if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                {
                    sql += " and 代理商名称 like '%" + 选中客户?.Text + "%'";
                }
                sql += " and 部门 in " + 部门清单;
                sql += " and 事业部='" + 事业部 + "'";
                sql += " group by 代理商名称,部门,销售员,类别  ";


                DataTable dataTableHZ2 = _SqlSugarScope.Ado.GetDataTable(sql);
                if (dataTableHZ2 != null && dataTableHZ2.Rows.Count > 0)
                {
                    //dt第一列插入序号列
                    if (dataTableHZ2.Columns.Contains("序号") == false)
                    {
                        dataTableHZ2.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                    }
                    for (int i = 0; i < dataTableHZ2.Rows.Count; i++)
                    {
                        dataTableHZ2.Rows[i]["序号"] = i + 1;
                    }

                }
                sql = " delete T客户发货 where  guid='" + guid + "' ";

                _SqlSugarScope.Ado.ExecuteCommand(sql);
                

                DataTableDynamicContextDetail = new DataTableDynamicContext(dataTableHZ2, (context, col) =>
               {
                   //所有列可排序
                   col.Sortable = true;
                   col.DefaultSort = true;
                   if (col.GetFieldName() == "序号")
                   {
                       col.Width = 40;
                   }
               });
            }

            await ToastService.Information("提示", "查询成功");



        }
        else
        {
            await ToastService.Success("提示", "选择的日期有误", autoHide: true);
        }
    }
}
